if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetArticlesByKey]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetArticlesByKey]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetArticlesByScripture]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetArticlesByScripture]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetArticlesByTopic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetArticlesByTopic]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetUserArticles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetUserArticles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_InsertArticle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_InsertArticle]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetArticlesByKey
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@Key		nvarchar(200)
)
 AS
SELECT * FROM v_articles WHERE NOT ApproveUserID IS NULL AND Type='post' AND Subject LIKE '%' + @Key +'%'
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetArticlesByScripture 
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@Title		nvarchar(50),
	@Chapter	smallint,
	@Section	smallint,
	@Chapter_to	smallint,
	@Section_to 	smallint
)
 AS

	SELECT * FROM v_articles 
	 WHERE NOT ApproveUserID IS NULL AND Type='post' AND (BibleTitle=@Title
	   	AND NOT ((@Chapter_to=BibleChapter AND @Section_to<BibleSection) 
		    OR (@Chapter_to<BibleChapter))
	   	AND NOT ((@Chapter=BibleChapter_To AND @Section>BibleSection_To) 
		    OR (@Chapter>BibleChapter_To)))
	   OR (PostID IN (SELECT PostID FROM PostBible WHERE BibleTitle=@Title
	   	AND NOT ((@Chapter_to=BibleChapter AND @Section_to<BibleSection) 
		    OR (@Chapter_to<BibleChapter))
	   	AND NOT ((@Chapter=BibleChapter_To AND @Section>BibleSection_To) 
		    OR (@Chapter>BibleChapter_To))))
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetArticlesByTopic
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@Topic		nvarchar(50),
	@SubTopic	nvarchar(50)
)
 AS
	SELECT * FROM v_articles WHERE NOT ApproveUserID IS NULL AND Type='post' AND Topic=@Topic AND SubTopic=@SubTopic
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetUserArticles
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@UserID	integer
)
 AS
	SELECT * FROM v_articles WHERE NOT ApproveUserID IS NULL AND Type='post' AND Userid=@UserID;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [sp_InsertArticle]
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@UserID		integer,
	@Subject		nvarchar(200),
	@Body			ntext,
	@Title			nvarchar(50),
	@Chapter		smallint,
	@Section		smallint,
	@Chapter_to		smallint,
	@Section_to		smallint,
	@Topic			nvarchar(50),
	@SubTopic		nvarchar(50),
	@Url			nvarchar(300),
	@ReturnID		int=-1	output 
)
AS 
	DECLARE @CategoryID integer, @CreateDate datetime, @NumberOfVisits smallint, @Status varchar(50), @Type varchar(10);
	SET @CategoryID=44;			
	SET @CreateDate=getdate();
	SET @NumberOfVisits=1;
	SET @Type='post';
	SET @Status='Pending';

	SET NOCOUNT OFF; 
	INSERT INTO ForumPosts (CategoryID, Subject, Body, UserID, CreateDate, NumberOfVisits, Status, Type)
		values (	@CategoryID, @Subject, @Body, @UserID, @CreateDate, @NumberOfVisits, @Status, @Type);

	DECLARE @PostID INTEGER;
	SET @PostID=@@IDENTITY;

	INSERT INTO Article (PostID, Topic, SubTopic, BibleTitle, BibleChapter, BibleSection, BibleChapter_To, BibleSection_To, url)
	values (
		@PostID, @Topic, @SubTopic, @Title, @Chapter, @Section, @Chapter_to, @Section_to, @Url);

	SELECT @ReturnID=@PostID;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

